package Connection;

import BasicClass.Bill;
import BasicClass.User;
import DAO.BillDao;
import DAO.DaoException;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class BillDaoImpl implements BillDao {
    public MyConnection myConnection;
    public BillDaoImpl(MyConnection myConnection){this.myConnection = myConnection;}

    @Override
    public void addBill(Bill bill) throws DaoException {
        String getCount = "SELECT COUNT(*) FROM bill";
        String sql = "INSERT INTO bill VALUE (?, ?, ?, ?, ?, ?)";
        int count = 0;
        ResultSet rst;
        try{
            PreparedStatement pstmt = myConnection.getConnection().prepareStatement(getCount);
            rst = pstmt.executeQuery();
            if(rst.next()){
                count = rst.getInt(1);
                pstmt = myConnection.getConnection().prepareStatement(sql);
                pstmt.setInt(1, count+1);
                pstmt.setString(2, bill.getISBN());
                pstmt.setString(3, bill.getAccount());
                pstmt.setInt(4, bill.getNumber());
                pstmt.setDouble(5, bill.getPrice());
                pstmt.setString(6, bill.getDate());
                pstmt.executeUpdate();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void updateBill(Bill bill) throws DaoException {

    }

    @Override
    public void deleteBill(Bill bill) throws DaoException {
        String sql = "DELETE FROM bill WHERE B_Id = ?";
        try{
            PreparedStatement preparedStatement = myConnection.getConnection().prepareStatement(sql);
            preparedStatement.setInt(1, bill.getId());
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public ArrayList<Bill> getBill(Bill bill, User Account, String SelectSearchManner, int choose) throws DaoException {
        ArrayList<Bill> arrayList = new ArrayList<>();
        String sql = "SELECT * FROM V_bill";
        switch (SelectSearchManner){
            case "账号":
                sql+=" WHERE A_Number = ?";
                break;
            case "书名":
                if(choose==6) sql+=" WHERE B_Name = ? AND A_Number = ?";
                else if(choose==8) sql+=" WHERE B_Name = ?";
                break;
            case "姓名":
                if(choose==6) sql += " WHERE C_Name = ? AND A_Number = ?";
                else if(choose==8) sql += " WHERE C_Name = ?";
                break;
            default:
        }
        ResultSet rst;
        try{
            PreparedStatement psmst = myConnection.getConnection().prepareStatement(sql);
            switch (SelectSearchManner){
                case "账号":
                    psmst.setString(1, Account.getNumber());
                    break;
                case "书名":
                    if(choose==6){
                        psmst.setString(1, bill.getBookName());
                        psmst.setString(2, Account.getNumber());
                    }
                    else psmst.setString(1, bill.getBookName());
                    break;
                case "姓名":
                    if(choose==6){
                        psmst.setString(1, bill.getAccountName());
                        psmst.setString(2, Account.getNumber());
                    }
                    else psmst.setString(1, bill.getAccountName());
                    break;
                default:
            }
            rst = psmst.executeQuery();
            while (rst.next()){
                Bill addBill = new Bill(
                        rst.getInt(1), rst.getString(2), rst.getString(3),
                        rst.getString(4), rst.getString(5),rst.getInt(6),
                        rst.getDouble(7),rst.getString(8)
                );
                arrayList.add(addBill);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return arrayList;
    }

    @Override
    public ArrayList<Bill> getAllBill() throws DaoException {
        ArrayList<Bill> arrayList = new ArrayList<>();
        String sql = "SELECT * FROM V_bill";
        ResultSet rst;
        try{
            PreparedStatement psmst = myConnection.getConnection().prepareStatement(sql);
            rst = psmst.executeQuery();
            while (rst.next()){
                Bill addBill = new Bill(
                        rst.getInt(1), rst.getString(2), rst.getString(3),
                        rst.getString(4), rst.getString(5),rst.getInt(6),
                        rst.getDouble(7),rst.getString(8)
                );
                arrayList.add(addBill);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return arrayList;
    }
}
